<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class HRStatsController extends Controller
{
    /**
     * 获取当前年份的人力资源统计数据
     * 
     * @return \Illuminate\Http\JsonResponse
     */
    public function getYearlyStats()
    {
        $currentYear = Carbon::now()->year;
        
        // 初始化12个月的数据结构
        $monthlyStats = [];
        for ($month = 1; $month <= 12; $month++) {
            $monthlyStats[$month] = [
                'month' => $month,
                'enrollments' => 0,  // 入职人数
                'separations' => 0,  // 离职人数
                'labor_cost' => 0    // 人力成本（元）
            ];
        }
        
        // 统计入职人数
        $enrollments = DB::table('enrollments as e1')
            ->select(
                DB::raw('MONTH(e1.hired_at) as month'),
                DB::raw('COUNT(DISTINCT e1.employee_id) as count')
            )
            ->where('e1.status', 4)
            ->whereYear('e1.hired_at', $currentYear)
            ->whereNull('e1.deleted_at')
            ->whereRaw('e1.id = (
                SELECT MAX(e2.id) 
                FROM enrollments e2 
                WHERE e2.employee_id = e1.employee_id 
                AND e2.status = 4 
                AND e2.deleted_at IS NULL
            )')
            ->groupBy(DB::raw('MONTH(e1.hired_at)'))
            ->get();
            
        foreach ($enrollments as $enrollment) {
            $monthlyStats[$enrollment->month]['enrollments'] = $enrollment->count;
        }
        
        // 统计离职人数
        $separations = DB::table('employees')
            ->select(
                DB::raw('MONTH(separated_at) as month'),
                DB::raw('COUNT(*) as count')
            )
            ->whereYear('separated_at', $currentYear)
            ->whereNotNull('separated_at')
            ->whereNull('deleted_at')
            ->groupBy(DB::raw('MONTH(separated_at)'))
            ->get();
            
        foreach ($separations as $separation) {
            $monthlyStats[$separation->month]['separations'] = $separation->count;
        }
        
        // 统计人力成本 - 按月汇总：基本工资+加班+奖金+绩效+社保+其他
        $laborCosts = DB::table('salary_details as sd')
            ->select(
                DB::raw('MONTH(s.period_at) as month'),
                'sd.salary_field_id',
                DB::raw('SUM(sd.amount) as total_amount')
            )
            ->leftJoin('salaries as s', 'sd.salary_id', '=', 's.id')
            ->whereNull('sd.deleted_at')
            ->whereNull('s.deleted_at')
            ->whereYear('s.period_at', $currentYear)
            ->groupBy(DB::raw('MONTH(s.period_at)'), 'sd.salary_field_id')
            ->get();
            
        // 按月汇总各项人力成本
        $monthlyLaborCosts = [];
        foreach ($laborCosts as $cost) {
            $month = $cost->month;
            $fieldId = $cost->salary_field_id;
            $amount = $cost->total_amount;
            
            if (!isset($monthlyLaborCosts[$month])) {
                $monthlyLaborCosts[$month] = 0;
            }
            
            // 根据field_id分类汇总
            switch ($fieldId) {
                case 1:    // 基本工资
                case 2:    // 加班
                case 6:    // 奖金
                case 5:    // 绩效
                case 3:    // 社保补贴
                case 8:    // 社保补贴
                default:   // 其他
                    $monthlyLaborCosts[$month] += $amount;
                    break;
            }
        }
        
        // 更新monthlyStats中的labor_cost
        foreach ($monthlyLaborCosts as $month => $totalCost) {
            // 将分转换为元，保留2位小数
            $monthlyStats[$month]['labor_cost'] = round($totalCost / 100, 2);
        }
        
        // 转换为数组格式
        $result = array_values($monthlyStats);
        
        // 计算年度汇总
        $yearlyTotal = [
            'total_enrollments' => array_sum(array_column($result, 'enrollments')),
            'total_separations' => array_sum(array_column($result, 'separations')),
            'total_labor_cost' => array_sum(array_column($result, 'labor_cost'))
        ];
        
        return response()->json([
            'code' => 0,
            'msg' => '人力资源统计数据获取成功',
            'data' => [
                'year' => $currentYear,
                'monthly_stats' => $result,
                'yearly_total' => $yearlyTotal
            ],
        ]);
    }

    public function getIncomeStats()
    {
        $currentYear = Carbon::now()->year;
        $lastMonth = Carbon::now()->subMonth();
        $lastMonthYear = $lastMonth->year;
        $lastMonthNumber = $lastMonth->month;
        
        // 定义收入区间（以分为单位）
        $incomeRanges = [
            ['label' => '2000以下', 'min' => 0, 'max' => 199999],
            ['label' => '2000-3000', 'min' => 200000, 'max' => 299999],
            ['label' => '3000-5000', 'min' => 300000, 'max' => 499999],
            ['label' => '5000-8000', 'min' => 500000, 'max' => 799999],
            ['label' => '8000-10000', 'min' => 800000, 'max' => 999999],
            ['label' => '10000-15000', 'min' => 1000000, 'max' => 1499999],
            ['label' => '15000-20000', 'min' => 1500000, 'max' => 1999999],
            ['label' => '20000以上', 'min' => 2000000, 'max' => PHP_INT_MAX],
        ];
        
        // 获取上月数据 - 每个员工在该月的总实发工资
        $lastMonthData = DB::table('salaries')
            ->select('employee_id', DB::raw('SUM(actual) as total_actual'))
            ->whereYear('period_at', $lastMonthYear)
            ->whereMonth('period_at', $lastMonthNumber)
            ->whereNull('deleted_at')
            ->groupBy('employee_id')
            ->get();
            
        // 获取本年度数据 - 每个员工在年度内的总实发工资
        $currentYearData = DB::table('salaries')
            ->select('employee_id', DB::raw('SUM(actual) as total_actual'))
            ->whereYear('period_at', $currentYear)
            ->whereNull('deleted_at')
            ->groupBy('employee_id')
            ->get();
            
        // 统计上月各区间人数
        $lastMonthStats = [];
        foreach ($incomeRanges as $range) {
            $count = $lastMonthData->where('total_actual', '>=', $range['min'])
                                  ->where('total_actual', '<=', $range['max'])
                                  ->count();
            $lastMonthStats[] = [
                'range' => $range['label'],
                'count' => $count,
                'min_amount' => $range['min'] / 100, // 转换为元
                'max_amount' => $range['max'] === PHP_INT_MAX ? null : $range['max'] / 100
            ];
        }
        
        // 统计本年度各区间人数
        $currentYearStats = [];
        foreach ($incomeRanges as $range) {
            $count = $currentYearData->where('total_actual', '>=', $range['min'])
                                    ->where('total_actual', '<=', $range['max'])
                                    ->count();
            $currentYearStats[] = [
                'range' => $range['label'],
                'count' => $count,
                'min_amount' => $range['min'] / 100, // 转换为元
                'max_amount' => $range['max'] === PHP_INT_MAX ? null : $range['max'] / 100
            ];
        }
        
        return response()->json([
            'code' => 0,
            'msg' => '公司员工收入区间统计数据获取成功',
            'data' => [
                'last_month' => [
                    'period' => $lastMonth->format('Y-m'),
                    'total_employees' => $lastMonthData->count(),
                    'income_distribution' => $lastMonthStats
                ],
                'current_year' => [
                    'period' => $currentYear,
                    'total_employees' => $currentYearData->count(),
                    'income_distribution' => $currentYearStats
                ]
            ],
        ]);
    }

    /**
     * 统计公司员工学历
     * 
     * @return \Illuminate\Http\JsonResponse
     */ 
    public function getEducationStats()
    {
        // 学历映射（与 Enrollment 模型中的 $eduMap 保持一致）
        $eduMap = [
            1 => '小学',
            2 => '初中',
            3 => '高中',
            4 => '中专',
            5 => '大专',
            6 => '本科',
        ];
        
        // 获取所有员工的学历统计 - 每个员工只取最新的入职记录
        $educationStats = DB::table('enrollments as e1')
            ->select(
                'e1.education',
                DB::raw('COUNT(DISTINCT e1.employee_id) as count')
            )
            ->where('e1.status', 4) // 只统计状态为4（生效中）的员工
            ->whereNotNull('e1.education')
            ->whereNull('e1.deleted_at')
            ->whereRaw('e1.id = (
                SELECT MAX(e2.id) 
                FROM enrollments e2 
                WHERE e2.employee_id = e1.employee_id 
                AND e2.status = 4 
                AND e2.deleted_at IS NULL
            )')
            ->groupBy('e1.education')
            ->get();
        
        // 计算总人数
        $totalCount = $educationStats->sum('count');
        
        // 格式化结果，计算百分比
        $result = [];
        foreach ($educationStats as $stat) {
            $educationName = $eduMap[$stat->education] ?? '未知';
            $percentage = $totalCount > 0 ? round(($stat->count / $totalCount) * 100, 1) : 0;
            $result[] = [
                'education_level' => $stat->education,
                'education_name' => $educationName,
                'count' => $stat->count,
                'percentage' => $percentage,
                'label' => $educationName . '：' . $percentage . '%'
            ];
        }
        
        // 按学历级别排序（从高到低）
        usort($result, function($a, $b) {
            return $b['education_level'] - $a['education_level'];
        });
        
        return response()->json([
            'code' => 0,
            'msg' => '公司员工学历结构统计数据获取成功',
            'data' => [
                'total_employees' => $totalCount,
                'education_distribution' => $result
            ],
        ]);
    }

    /**
     * 统计公司员工性别
     * 
     * @return \Illuminate\Http\JsonResponse
     */    
    public function getGenderStats()
    {
        $genderStats = DB::table('enrollments')
            ->select('gender', DB::raw('COUNT(*) as count'))
            ->whereNotNull('gender')
            ->where('status', 4)
            ->groupBy('gender')
            ->get();

        $totalCount = DB::table('enrollments')
            ->where('status', 4)
            ->count();

            return response()->json([
                'code' => 0,
                'msg' => '公司员工性别结构统计数据获取成功',
                'data' => [
                    'total_employees' => $totalCount,
                    'gender_distribution' => $genderStats
                ],
            ]);
    }

    /**
     * 统计公司员工年龄分布
     * 
     * @return \Illuminate\Http\JsonResponse
     */
    public function getAgeStats()
    {
        // 定义年龄区间
        $ageRanges = [
            ['label' => '20岁及以下', 'min' => 0, 'max' => 20],
            ['label' => '21-30岁', 'min' => 21, 'max' => 30],
            ['label' => '31-40岁', 'min' => 31, 'max' => 40],
            ['label' => '41-50岁', 'min' => 41, 'max' => 50],
            ['label' => '51岁及以上', 'min' => 51, 'max' => 200],
        ];
        
        // 获取所有员工的年龄数据 - 每个员工只取最新的入职记录
        $enrollments = DB::table('enrollments as e1')
            ->select('e1.birthed_at')
            ->whereNotNull('e1.birthed_at')
            ->whereNull('e1.deleted_at')
            ->whereRaw('e1.id = (
                SELECT MAX(e2.id) 
                FROM enrollments e2 
                WHERE e2.employee_id = e1.employee_id 
                AND e2.deleted_at IS NULL
            )')
            ->get();
            
        $currentDate = Carbon::now();
        $ageGroups = [];
        
        // 初始化年龄组统计
        foreach ($ageRanges as $range) {
            $ageGroups[$range['label']] = 0;
        }
        
        // 计算每个员工的年龄并分组
        foreach ($enrollments as $enrollment) {
            $birthDate = Carbon::parse($enrollment->birthed_at);
            $age = $birthDate->diffInYears($currentDate);
            
            // 根据年龄分配到对应区间
            foreach ($ageRanges as $range) {
                if ($age >= $range['min'] && $age <= $range['max']) {
                    $ageGroups[$range['label']]++;
                    break;
                }
            }
        }
        
        // 计算总人数
        $totalCount = array_sum($ageGroups);
        
        // 格式化结果，计算百分比
        $result = [];
        foreach ($ageGroups as $ageRange => $count) {
            $percentage = $totalCount > 0 ? round(($count / $totalCount) * 100, 1) : 0;
            $result[] = [
                'age_range' => $ageRange,
                'count' => $count,
                'percentage' => $percentage . '%'
            ];
        }
        
        return response()->json([
            'code' => 0,
            'msg' => '公司员工年龄分布统计数据获取成功',
            'data' => [
                'total_employees' => $totalCount,
                'age_distribution' => $result
            ],
        ]);
    }

    /**
     * 统计公司员工工龄分布
     * 
     * @return \Illuminate\Http\JsonResponse
     */
    public function getWorkAgeStats()
    {
        // 定义工龄区间
        $workAgeRanges = [
            ['label' => '不满一年', 'min' => 0, 'max' => 0.99],
            ['label' => '1-2年', 'min' => 1, 'max' => 1.99],
            ['label' => '2-5年', 'min' => 2, 'max' => 4.99],
            ['label' => '5-10年', 'min' => 5, 'max' => 9.99],
            ['label' => '10年以上', 'min' => 10, 'max' => 100],
        ];
        
        // 获取所有status为4的员工的入职数据 - 每个员工只取最新的入职记录
        $enrollments = DB::table('enrollments as e1')
            ->select('e1.hired_at')
            ->where('e1.status', 4)
            ->whereNotNull('e1.hired_at')
            ->whereNull('e1.deleted_at')
            ->whereRaw('e1.id = (
                SELECT MAX(e2.id) 
                FROM enrollments e2 
                WHERE e2.employee_id = e1.employee_id 
                AND e2.status = 4
                AND e2.deleted_at IS NULL
            )')
            ->get();
            
        $currentDate = Carbon::now();
        $workAgeGroups = [];
        
        // 初始化工龄组统计
        foreach ($workAgeRanges as $range) {
            $workAgeGroups[$range['label']] = 0;
        }
        
        // 计算每个员工的工龄并分组
        foreach ($enrollments as $enrollment) {
            $hiredDate = Carbon::parse($enrollment->hired_at);
            $workYears = $hiredDate->diffInYears($currentDate, true); // true表示精确到小数
            
            // 根据工龄分配到对应区间
            foreach ($workAgeRanges as $range) {
                if ($workYears >= $range['min'] && $workYears <= $range['max']) {
                    $workAgeGroups[$range['label']]++;
                    break;
                }
            }
        }
        
        // 计算总人数
        $totalCount = array_sum($workAgeGroups);
        // 格式化结果，计算百分比
        $result = [];
        foreach ($workAgeGroups as $workAgeRange => $count) {
            $percentage = $totalCount > 0 ? round(($count / $totalCount) * 100, 1) : 0;
            $result[] = [
                'work_age_range' => $workAgeRange,
                'count' => $count,
                'percentage' => $percentage . '%'
            ];
        }
        
        return response()->json([
            'code' => 0,
            'msg' => '公司员工工龄分布统计数据获取成功',
            'data' => [
                'work_age_distribution' => $result
            ],
        ]);
    }

    /**
     * 管理岗/普通岗绩效情况明细
     * 
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getManagerPerformanceStats(Request $request)
    {
        $page = $request->get('page', 1);
        $type = $request->get('type', 1);
        $perPage = 7;
        
        // 定义绩效类型映射
        $typeMap = [
            0 => '工作态度',
            1 => '工作能力',
            2 => '月度业绩',
            3 => '管理体系',
            4 => '人才培养',
            5 => '人才流失',
        ];
        
        // 查询管理岗的绩效评估数据，按分页获取
        $query = DB::table('performance_assessments as pa')
            ->select(
                'pa.id as assessment_id',
                'pa.employee_id',
                'pa.title',
                'pa.overall_rating',
                'e.name as employee_name'
            )
            ->leftJoin('enrollments as e', function($join) {
                $join->on('pa.employee_id', '=', 'e.employee_id')
                     ->where('e.status', 4)
                     ->whereNull('e.deleted_at')
                     ->whereRaw('e.id = (
                         SELECT MAX(e2.id) 
                         FROM enrollments e2 
                         WHERE e2.employee_id = e.employee_id 
                         AND e2.status = 4 
                         AND e2.deleted_at IS NULL
                     )');
            })
            ->where('pa.type', $type) // 1管理岗 2普通岗
            ->whereNull('pa.deleted_at')
            ->orderBy('pa.id', 'desc');
            
        // 获取总数
        $total = $query->count();
        
        // 分页获取数据
        $assessments = $query->skip(($page - 1) * $perPage)
                           ->take($perPage)
                           ->get();
        
        // 获取所有评估ID
        $assessmentIds = $assessments->pluck('assessment_id')->toArray();

        // 获取对应的绩效项目数据
        $performanceItems = [];
        if (!empty($assessmentIds)) {
            $performanceItems = DB::table('performance_items')
                ->select('performance_assessment_id', 'type', 'rating')
                ->whereIn('performance_assessment_id', $assessmentIds)
                ->whereNull('deleted_at')
                ->get()
                ->groupBy('performance_assessment_id');
        }
     
        // 格式化结果
        $result = [];
        foreach ($assessments as $assessment) {
            $assessmentData = [
                'assessment_id' => $assessment->assessment_id,
                'employee_id' => $assessment->employee_id,
                'employee_name' => $assessment->employee_name ?? '未知',
                'title' => $assessment->title,
                'work_attitude' => 0,      // 工作态度
                'total_score' => 0,    // 总得分
                'monthly_performance' => 0, // 月度业绩
                'management_system' => 0,   // 管理体系
                'talent_development' => 0,  // 人才培养
                'talent_loss' => 0,        // 人才流失
            ];

            // 如果有实际的绩效项目数据，更新对应的分值
            if (isset($performanceItems[$assessment->assessment_id])) {
                $items = $performanceItems[$assessment->assessment_id];
                foreach ($items as $item) {
                    switch ($item->type) {
                        case 0:
                            $assessmentData['work_attitude'] = $item->rating;
                            break;
                        case 2:
                            $assessmentData['monthly_performance'] = $item->rating;
                            break;
                        case 3:
                            $assessmentData['management_system'] = $item->rating;
                            break;
                        case 4:
                            $assessmentData['talent_development'] = $item->rating;
                            break;
                        case 5:
                            $assessmentData['talent_loss'] = $item->rating;
                            break;
                    }
                }
                $assessmentData['total_score'] = $assessmentData['work_attitude'] + $assessmentData['monthly_performance'] + $assessmentData['management_system'] + $assessmentData['talent_development'] + $assessmentData['talent_loss'];
            }
            
            $result[] = $assessmentData;
        }
        
        // 计算分页信息
        $totalPages = ceil($total / $perPage);
        $hasNextPage = $page < $totalPages;
        $hasPrevPage = $page > 1;
        
        return response()->json([
            'code' => 0,
            'msg' => '管理岗绩效统计数据获取成功',
            'data' => [
                'list' => $result,
                'pagination' => [
                    'current_page' => $page,
                    'per_page' => $perPage,
                    'total' => $total,
                    'total_pages' => $totalPages,
                    'has_next_page' => $hasNextPage,
                    'has_prev_page' => $hasPrevPage
                ]
            ],
        ]);
    }

    public function getEmployeeTypeStats()
    {
        // 员工类型映射（与 Enrollment 模型中的 $typeMap 保持一致）
        $typeMap = [
            1   =>  '全职',
            2   =>  '兼职',
            3   =>  '实习',
            4   =>  '外包',
            5   =>  '劳务'
        ];
        
        // 获取所有员工的类型统计 - 每个员工只取最新的入职记录
        $typeStats = DB::table('enrollments as e1')
            ->select(
                'e1.type',
                DB::raw('COUNT(DISTINCT e1.employee_id) as count')
            )
            ->where('e1.status', 4) // 只统计状态为4（生效中）的员工
            ->whereNotNull('e1.type')
            ->whereNull('e1.deleted_at')
            ->whereRaw('e1.id = (
                SELECT MAX(e2.id) 
                FROM enrollments e2 
                WHERE e2.employee_id = e1.employee_id 
                AND e2.status = 4 
                AND e2.deleted_at IS NULL
            )')
            ->groupBy('e1.type')
            ->get();
        
        // 计算总人数
        $totalCount = $typeStats->sum('count');
        
        // 初始化所有类型的统计数据
        $result = [];
        foreach ($typeMap as $typeId => $typeName) {
            $result[$typeId] = [
                'type_id' => $typeId,
                'type_name' => $typeName,
                'count' => 0,
                'percentage' => 0,
                'label' => $typeName . '：0%'
            ];
        }
        
        // 更新实际统计数据
        foreach ($typeStats as $stat) {
            if (isset($result[$stat->type])) {
                $percentage = $totalCount > 0 ? round(($stat->count / $totalCount) * 100, 1) : 0;
                $result[$stat->type] = [
                    'type_id' => $stat->type,
                    'type_name' => $typeMap[$stat->type],
                    'count' => $stat->count,
                    'percentage' => $percentage,
                    'label' => $typeMap[$stat->type] . '：' . $percentage . '%'
                ];
            }
        }
        
        // 按类型ID排序并转换为索引数组
        ksort($result);
        $result = array_values($result);
        
        return response()->json([
            'code' => 0,
            'msg' => '公司员工类型分布统计数据获取成功',
            'data' => [
                'total_employees' => $totalCount,
                'type_distribution' => $result
            ],
        ]);
    }

    /**
     * 统计公司员工总人数
     * 
     * @return \Illuminate\Http\JsonResponse
     */
    public function getTotalEmployeeStats()
    {
        $totalCount = DB::table('enrollments')->where('status', 4)->count();
        return response()->json([
            'code' => 0,
            'msg' => '公司员工总人数统计数据获取成功',
            'data' => ['total_employees' => $totalCount],
        ]);
    }

    /**
     * 从身份证计算退休年份
     * @param string $idCard 身份证号码
     * @param bool $isFemaleCadre 女性是否为干部
     * @return array 退休信息或错误信息
     */
    private function calcRetirementYear($idCard, $isFemaleCadre = false)
    {
        // 验证身份证号码格式
        if (!preg_match('/^\d{17}[\dXx]$/i', $idCard)) {
            return ['error' => '无效身份证号码'];
        }
        
        $id = strtoupper($idCard);
        
        // 解析出生日期
        $birthYear = intval(substr($id, 6, 4));
        $birthMonth = intval(substr($id, 10, 2));
        $birthDay = intval(substr($id, 12, 2));
        
        // 基础参数设置
        $policyStartDate = new Carbon('2025-01-01');
        
        // 解析性别
        $genderCode = intval($id[16]);
        $isMale = $genderCode % 2 === 1;
        
        // 原退休年龄判断
        $originalAge = 60;
        $targetAge = 63;
        $delayRate = 4;
        
        if (!$isMale) {
            $originalAge = $isFemaleCadre ? 55 : 50;
            $targetAge = $isFemaleCadre ? 58 : 55;
            $delayRate = $isFemaleCadre ? 4 : 2;
        }
        
        // 计算原退休日期
        $birthDate = Carbon::createFromDate($birthYear, $birthMonth, $birthDay);
        $originalRetirementDate = $birthDate->copy()->addYears($originalAge);
        
        // 判断是否受延迟政策影响
        if ($originalRetirementDate->lt($policyStartDate)) {
            return ['error' => '不受延迟政策影响'];
        }
        
        // 计算延迟月数
        $delayYears = ($targetAge - $originalAge) * 12;
        $monthsDiff = ($originalRetirementDate->year - $policyStartDate->year) * 12 + $birthMonth + 1;
        $delayMonths = min(ceil($monthsDiff / $delayRate), $delayYears);
        
        // 计算实际退休年龄（年+月）
        $delayYearsDecimal = $delayMonths / 12;
        $actualRetireAge = $originalAge + $delayYearsDecimal;
        
        // 计算精确退休年份（考虑出生月份）
        $retireDate = $birthDate->copy();
        $retireDate->addYears(floor($actualRetireAge));
        $retireDate->addMonths(round(($actualRetireAge - floor($actualRetireAge)) * 12));
        
        $month = $delayMonths % 12;
        
        return [
            'actualRetireAge' => floor($actualRetireAge) . ' 岁 ' . $month . ' 个月',
            'retirementYear' => $retireDate->year,
            'retirementMonth' => $retireDate->month,
            'originalAge' => $originalAge,
            'delayMonths' => $delayMonths
        ];
    }

    /**
     * 统计公司员工预退休人数（5年内退休）
     * 
     * @return \Illuminate\Http\JsonResponse
     */
    public function getPreRetirementStats()
    {
        $currentYear = Carbon::now()->year;
        $targetYear = $currentYear + 5; // 5年内退休
        
        // 获取所有员工的身份证和相关信息
        $employees = DB::table('enrollments as e1')
            ->select(
                'e1.employee_id',
                'e1.name',
                'e1.id_number',
                'e1.gender',
                'e1.education',
                'e1.birthed_at'
            )
            ->where('e1.status', 4)
            ->whereNotNull('e1.id_number')
            ->whereNull('e1.deleted_at')
            ->whereRaw('e1.id = (
                SELECT MAX(e2.id) 
                FROM enrollments e2 
                WHERE e2.employee_id = e1.employee_id 
                AND e2.status = 4 
                AND e2.deleted_at IS NULL
            )')
            ->get();
        
        $preRetirementEmployees = [];
        $totalCount = 0;
        
        foreach ($employees as $employee) {
            $retirementInfo = $this->calcRetirementYear($employee->id_number);
            
            // 如果计算成功且在5年内退休
            if (!isset($retirementInfo['error']) && 
                $retirementInfo['retirementYear'] <= $targetYear) {
                $preRetirementEmployees[] = [
                    'employee_id' => $employee->employee_id,
                    'name' => $employee->name,
                    'retirement_year' => $retirementInfo['retirementYear'],
                    'retirement_month' => $retirementInfo['retirementMonth'],
                    'actual_retire_age' => $retirementInfo['actualRetireAge'],
                    'delay_months' => $retirementInfo['delayMonths']
                ];
                $totalCount++;
            }
        }
        
        // 按退休年份排序
        usort($preRetirementEmployees, function($a, $b) {
            if ($a['retirement_year'] == $b['retirement_year']) {
                return $a['retirement_month'] - $b['retirement_month'];
            }
            return $a['retirement_year'] - $b['retirement_year'];
        });
        
        // 按年份统计
        $yearlyStats = [];
        foreach ($preRetirementEmployees as $employee) {
            $year = $employee['retirement_year'];
            if (!isset($yearlyStats[$year])) {
                $yearlyStats[$year] = [
                    'year' => $year,
                    'count' => 0,
                    'employees' => []
                ];
            }
            $yearlyStats[$year]['count']++;
            $yearlyStats[$year]['employees'][] = $employee;
        }
        
        return response()->json([
            'code' => 0,
            'msg' => '公司员工预退休人数统计数据获取成功',
            'data' => [
                'total_pre_retirement' => $totalCount,
                'target_year_range' => $currentYear . '-' . $targetYear,
                'yearly_distribution' => array_values($yearlyStats),
                'employee_list' => $preRetirementEmployees
            ],
        ]);
    }
} 